package com.eleadmin.ocean.biz.impl;

import com.eleadmin.common.system.entity.Role;
import com.eleadmin.common.system.entity.User;
import com.eleadmin.damon.utils.constant.UserConstant;
import com.eleadmin.damon.utils.results.BaseDao;
import com.eleadmin.damon.utils.results.PageResult2;
import com.eleadmin.damon.utils.utils.Utils;
import com.eleadmin.ocean.biz.OceanBulkCargoBiz;
import com.eleadmin.ocean.entity.OceanBulkCargo;
import com.eleadmin.common.core.web.JsonResult;
import com.eleadmin.ocean.param.OceanBulkCargoParam;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.Map;
import java.util.List;

/**
 * 数据层实现类
 *
 * @author : Doman
 * @version ：1.0
 * @date : 2023-10-25 15:36:44
 */
@Repository
public class OceanBulkCargoBizImpl extends BaseDao implements OceanBulkCargoBiz {

    @Override
    public JsonResult list(PageResult2<T> page, OceanBulkCargoParam oceanBulkCargo, User loginUser) throws Exception {
        Map<String, String> map = new HashMap<String, String>();
        RowMapper<OceanBulkCargo> rm = new BeanPropertyRowMapper<OceanBulkCargo>(OceanBulkCargo.class);
        StringBuffer sql = new StringBuffer("select o.id,o.organization_id,o.customer_id");
        sql.append(",o.customer,o.customer_code,o.services,o.fba");
        sql.append(",o.bill_code,o.contacts,o.phone,o.addr1");
        sql.append(",o.state,o.apply_customs_type,o.customs_clearance,o.pay_taxes_type");
        sql.append(",o.delivery_terms,o.purchase_insurance,o.box_nums_total,o.box_volume");
        sql.append(",o.box_weight,o.remark,o.create_time,o.create_user");
        sql.append(",o.total,o.total_unit, op.state processState");
        sql.append(",o.receive_state,o.pay_state,o.customer_remark,o.annex");
        sql.append(",o.salesmanId,o.salesman,o.operaterId,o.operater");
        sql.append(",o.update_time,o.update_user");
        sql.append(",sogzn.organization_name organizationName,suser.nickname createName,suser2.nickname updateName ");
        sql.append(" from chf_ocean_bulk_cargo o ");
        sql.append(" inner join sys_user suser on suser.user_id=o.create_user ");
        sql.append(" left join sys_user suser2 on suser2.user_id=o.update_user ");
        sql.append(" inner join ( ");
        sql.append(" SELECT op1.state,op1.bulk_cargo_id, op1.create_time,ROW_NUMBER() OVER (PARTITION BY op1.bulk_cargo_id ORDER BY op1.create_time DESC) AS row_num FROM chf_ocean_bulk_cargo_process op1 ORDER BY op1.create_time desc   ");
        sql.append(" ) op on op.bulk_cargo_id=o.id and op.row_num=1 ");
        sql.append(" inner join sys_organization sogzn on sogzn.organization_id=o.organization_id and sogzn.deleted=0 ");
        sql.append(" where o.deleted=0 ");
        if (loginUser.getUserId() != UserConstant.ADMIN_ID) {
            sql.append(" and o.organization_id=:tableOrganizationId ");
            map.put("tableOrganizationId", loginUser.getOrganizationId() + "");
        }
        if (Utils.isNotEmpty(oceanBulkCargo)) {
            if (Utils.isNotEmpty(oceanBulkCargo.getCustomerId())) {
                sql.append(" and o.customer_id=:customerId ");
                map.put("customerId", oceanBulkCargo.getCustomerId());
            }
            if (Utils.isNotEmpty(oceanBulkCargo.getCustomer())) {
                sql.append(" and instr(o.customer,:customer)>0 ");
                map.put("customer", oceanBulkCargo.getCustomer());
            }
            if (Utils.isNotEmpty(oceanBulkCargo.getBillCode())) {
                sql.append(" and (instr(o.bill_code,:bill_code)>0 or instr(o.customer_code,:bill_code)>0) ");
                map.put("bill_code", oceanBulkCargo.getBillCode());
            }
            if (Utils.isNotEmpty(oceanBulkCargo.getSort()) && Utils.isNotEmpty(oceanBulkCargo.getOrder())) {
                sql.append(" order by o." + oceanBulkCargo.getSort() + " " + oceanBulkCargo.getOrder());
            } else {
                sql.append(" order by o.create_time desc");
            }
        }
        return jsonResult(getPageQueryList(page, sql.toString(), map, rm));
    }

    @Override
    public List<OceanBulkCargo> list(OceanBulkCargoParam oceanBulkCargo, User loginUser) {
        Map<String, String> map = new HashMap<String, String>();
        RowMapper<OceanBulkCargo> rm = new BeanPropertyRowMapper<OceanBulkCargo>(OceanBulkCargo.class);
        StringBuffer sql = new StringBuffer("select o.id,o.organization_id,o.customer_id");
        sql.append(",o.customer,o.customer_code,o.services,o.fba");
        sql.append(",o.bill_code,o.contacts,o.phone,o.addr1");
        sql.append(",o.state,o.apply_customs_type,o.customs_clearance,o.pay_taxes_type");
        sql.append(",o.delivery_terms,o.purchase_insurance,o.box_nums_total,o.box_volume");
        sql.append(",o.box_weight,o.remark,o.create_time,o.create_user");
        sql.append(",o.total,o.total_unit");
        sql.append(",o.receive_state,o.pay_state,o.customer_remark,o.annex");
        sql.append(",o.salesmanId,o.salesman,o.operaterId,o.operater");
        sql.append(",o.update_time,o.update_user");
        sql.append(",sogzn.organization_name organizationName,suser.nickname createName ");
        sql.append(" from chf_ocean_bulk_cargo o ");
        sql.append(" inner join chf_customer cm on cm.id=o.customer_id ");
        sql.append(" inner join sys_user suser on suser.user_id=o.create_user ");
        sql.append(" inner join sys_organization sogzn on sogzn.organization_id=o.organization_id and sogzn.deleted=0 ");
        sql.append(" where o.deleted=0 ");
        if (loginUser.getUserId() != UserConstant.ADMIN_ID) {
            sql.append(" and o.organization_id=:tableOrganizationId ");
            map.put("tableOrganizationId", loginUser.getOrganizationId() + "");
        }
        boolean salesman = false;
        boolean operator = false;
        for(Role role : loginUser.getRoles()) {
            if(role.getRoleName().equals("业务员")){
                salesman = true;
            }
            if(role.getRoleName().equals("操作")){
                operator = true;
            }
            if(role.getRoleName().indexOf("调度")>-1 || role.getRoleName().indexOf("财务")>-1
                    || role.getRoleName().indexOf("管理")>-1){
                operator = false;
                salesman = false;
            }
        }
        if (Utils.isNotEmpty(oceanBulkCargo)) {
            if (salesman) {
                sql.append(" and o.salesmanId=:salesmanId ");
                map.put("salesmanId", loginUser.getUserId()+"");
            }
            if (operator) {
                sql.append(" and o.operaterId=:operaterId ");
                map.put("operaterId", loginUser.getUserId()+"");
            }
            if (Utils.isNotEmpty(oceanBulkCargo.getCustomerId())) {
                sql.append(" and o.customer_id=:customerId ");
                map.put("customerId", oceanBulkCargo.getCustomerId());
            }
            if (Utils.isNotEmpty(oceanBulkCargo.getCustomer())) {
                sql.append(" and (instr(cm.name,:name)>0 or instr(cm.pinyin,:customer)>0 ) ");
                map.put("customer", oceanBulkCargo.getCustomer());
            }
            if (Utils.isNotEmpty(oceanBulkCargo.getCustomerEq())) {
                sql.append(" and cm.name=:customerEq ");
                map.put("customerEq", oceanBulkCargo.getCustomerEq());
            }
            if (Utils.isNotEmpty(oceanBulkCargo.getFactoryPackDateSearch1())) {
                sql.append(" and o.create_time>=:factoryPackDateSearch1 ");
                map.put("factoryPackDateSearch1", oceanBulkCargo.getFactoryPackDateSearch1());
            }
            if (Utils.isNotEmpty(oceanBulkCargo.getFactoryPackDateSearch2())) {
                sql.append(" and o.create_time<=:factoryPackDateSearch2 ");
                map.put("factoryPackDateSearch2", oceanBulkCargo.getFactoryPackDateSearch2());
            }
            if (Utils.isNotEmpty(oceanBulkCargo.getSort()) && Utils.isNotEmpty(oceanBulkCargo.getOrder())) {
                sql.append(" order by o." + oceanBulkCargo.getSort() + " " + oceanBulkCargo.getOrder());
            } else {
                sql.append(" order by o.create_time desc");
            }
        }
        getMapParameter(sql, map);
        return jdbcTemplate.query(sql.toString(), map, rm);
    }

    @Override
    public List<OceanBulkCargo> listForCustomerFeeList(OceanBulkCargoParam oceanBulkCargoParam, User loginUser) {
        Map<String, Object> map = new HashMap<String, Object>();
        RowMapper<OceanBulkCargo> rm = new BeanPropertyRowMapper<OceanBulkCargo>(OceanBulkCargo.class);
        StringBuffer sql = new StringBuffer("select distinct o.id,o.organization_id,o.customer_id");
        sql.append(",if(o.customer=:customerEq2,o.customer,'') customer,o.customer_code,o.services,o.fba");
        sql.append(",o.bill_code,o.contacts,o.phone,o.addr1");
        sql.append(",o.state,o.apply_customs_type,o.customs_clearance,o.pay_taxes_type");
        sql.append(",o.delivery_terms,o.purchase_insurance,o.box_nums_total,o.box_volume");
        sql.append(",o.box_weight,o.remark,o.create_time,o.create_user");
        sql.append(",o.total,o.total_unit");
        sql.append(",o.receive_state,o.pay_state,o.customer_remark,o.annex");
        sql.append(",o.salesmanId,o.salesman,o.operaterId,o.operater");
        sql.append(",o.update_time,o.update_user");
        sql.append(" from chf_ocean_bulk_cargo o ");
        sql.append(" inner join chf_customer cm on cm.id=o.customer_id ");
        sql.append(" inner join chf_ocean_bulk_cargo_fee obc on obc.bulk_cargo_id=o.id ");
        sql.append(" where o.deleted=0 ");
        map.put("customerEq2", oceanBulkCargoParam.getAutocompletevalue());
        if (loginUser.getUserId() != UserConstant.ADMIN_ID) {
            sql.append(" and o.organization_id=:tableOrganizationId ");
            map.put("tableOrganizationId", loginUser.getOrganizationId() + "");
        }
        boolean salesman = false;
        boolean operator = false;
        for(Role role : loginUser.getRoles()) {
            if(role.getRoleName().equals("业务员")){
                salesman = true;
            }
            if(role.getRoleName().equals("操作")){
                operator = true;
            }
            if(role.getRoleName().indexOf("调度")>-1 || role.getRoleName().indexOf("财务")>-1
                    || role.getRoleName().indexOf("管理")>-1){
                operator = false;
                salesman = false;
            }
        }
        if (Utils.isNotEmpty(oceanBulkCargoParam)) {
            if (salesman) {
                sql.append(" and o.salesmanId=:salesmanId ");
                map.put("salesmanId", loginUser.getUserId()+"");
            }
            if (operator) {
                sql.append(" and o.operaterId=:operaterId ");
                map.put("operaterId", loginUser.getUserId()+"");
            }
            if (Utils.isNotEmpty(oceanBulkCargoParam.getCustomerId())) {
                sql.append(" and o.customer_id=:customerId ");
                map.put("customerId", oceanBulkCargoParam.getCustomerId());
            }
            if (Utils.isNotEmpty(oceanBulkCargoParam.getBillCode())) {
                String[] a = oceanBulkCargoParam.getBillCode().split(",");
//                StringBuffer c = new StringBuffer("'");
//                for (String aa : a) {
//                    c.append(aa+"','");
//                }
//                String cStr = c.toString().substring(0, c.length()-2);
                sql.append(" and o.bill_code in (:bill_code) ");
                map.put("bill_code", a);
            }
            if (Utils.isNotEmpty(oceanBulkCargoParam.getCustomer())) {
                sql.append(" and (instr(o.customer,:customer)>0 or instr(cm.pinyin,:customer)>0 ) ");
                map.put("customer", oceanBulkCargoParam.getCustomer());
            }
            if (Utils.isNotEmpty(oceanBulkCargoParam.getCustomerAbbreviation())) {
                sql.append(" and obc.invoicename=:invoicename ");
                map.put("invoicename", oceanBulkCargoParam.getCustomerAbbreviation());
            }
            if (Utils.isNotEmpty(oceanBulkCargoParam.getType())) {
                sql.append(" and obc.type=:type ");
                map.put("type", oceanBulkCargoParam.getType());
            }
            if (Utils.isNotEmpty(oceanBulkCargoParam.getFactoryPackDateSearch1())) {
                sql.append(" and o.create_time>=:factoryPackDateSearch1 ");
                map.put("factoryPackDateSearch1", oceanBulkCargoParam.getFactoryPackDateSearch1());
            }
            if (Utils.isNotEmpty(oceanBulkCargoParam.getFactoryPackDateSearch2())) {
                sql.append(" and o.create_time<=:factoryPackDateSearch2 ");
                map.put("factoryPackDateSearch2", oceanBulkCargoParam.getFactoryPackDateSearch2());
            }
            if (Utils.isNotEmpty(oceanBulkCargoParam.getCustomerEq())) {
                sql.append(" and o.customer=:customerEq ");
                map.put("customerEq", oceanBulkCargoParam.getCustomerEq());
            }
            if (Utils.isNotEmpty(oceanBulkCargoParam.getSalesman())) {
                sql.append(" and instr(o.salesman,:salesman)>0 ");
                map.put("salesman", oceanBulkCargoParam.getSalesman());
            }
            if (Utils.isNotEmpty(oceanBulkCargoParam.getOperater())) {
                sql.append(" and instr(o.operater,:operater)>0 ");
                map.put("operater", oceanBulkCargoParam.getOperater());
            }
            if (Utils.isNotEmpty(oceanBulkCargoParam.getSort()) && Utils.isNotEmpty(oceanBulkCargoParam.getOrder())) {
                sql.append(" order by o." + oceanBulkCargoParam.getSort() + " " + oceanBulkCargoParam.getOrder());
            } else {
                sql.append(" order by o.create_time desc");
            }
        }
//        getMapParameter(sql, map);
        return jdbcTemplate.query(sql.toString(), map, rm);
    }

    @Override
    public OceanBulkCargo getById(String id) {
        RowMapper<OceanBulkCargo> rm = new BeanPropertyRowMapper<OceanBulkCargo>(OceanBulkCargo.class);
        Map<String, String> map = new HashMap<String, String>();
        StringBuffer sql = new StringBuffer("select o.id,o.organization_id,o.customer_id");
        sql.append(",o.customer,o.customer_code,o.services,o.fba");
        sql.append(",o.bill_code,o.contacts,o.phone,o.addr1");
        sql.append(",o.state,o.apply_customs_type,o.customs_clearance,o.pay_taxes_type");
        sql.append(",o.delivery_terms,o.purchase_insurance,o.box_nums_total,o.box_volume");
        sql.append(",o.box_weight,o.remark,o.create_time,o.create_user");
        sql.append(",o.total,o.total_unit");
        sql.append(",o.receive_state,o.pay_state,o.customer_remark,o.annex");
        sql.append(",o.salesmanId,o.salesman,o.operaterId,o.operater");
        sql.append(",o.update_time,o.update_user");
        sql.append(",sogzn.organization_name organizationName,suser.nickname createName ");
        sql.append(" from chf_ocean_bulk_cargo o ");
        sql.append(" inner join sys_user suser on suser.user_id=o.create_user ");
        sql.append(" inner join sys_organization sogzn on sogzn.organization_id=o.organization_id and sogzn.deleted=0 ");
        sql.append(" where o.deleted=0 and o.id=:id ");
        map.put("id", id);
        getMapParameter(sql, map);
        return jdbcTemplate.queryForObject(sql.toString(), map, rm);
    }

    @Override
    public Integer save(OceanBulkCargo oceanBulkCargo, User loginUser) {
        GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
        KeyHolder keyHolder = new GeneratedKeyHolder();
        Map<String, String> map = new HashMap<String, String>();
        StringBuffer sql = new StringBuffer("insert into chf_ocean_bulk_cargo (");
        sql.append("organization_id,customer_id,customer,customer_code");
        sql.append(",bill_code,services,fba,contacts");
        sql.append(",phone,addr1");
        sql.append(",state,apply_customs_type");
        sql.append(",customs_clearance,pay_taxes_type,delivery_terms,purchase_insurance");
        sql.append(",box_nums_total,box_volume,box_weight,remark");
        sql.append(",total,total_unit");
        sql.append(",receive_state,pay_state,customer_remark,annex");
        sql.append(",salesmanId,salesman,operaterId,operater");
        sql.append(",create_user,create_time) values ( ");
        sql.append(":organization_id,:customer_id,:customer,:customer_code");
        sql.append(",:bill_code,:services,:fba,:contacts");
        sql.append(",:phone,:addr1");
        sql.append(",:state,:apply_customs_type");
        sql.append(",:customs_clearance,:pay_taxes_type,:delivery_terms,:purchase_insurance");
        sql.append(",:box_nums_total,:box_volume,:box_weight,:remark");
        sql.append(",:total,:total_unit");
        sql.append(",:receive_state,:pay_state,:customer_remark,:annex");
        sql.append(",:salesmanId,:salesman,:operaterId,:operater");
        sql.append(",:create_user,now())");
        map.put("organization_id", loginUser.getOrganizationId() + "");
        map.put("customer_id", oceanBulkCargo.getCustomerId() == null ? null : oceanBulkCargo.getCustomerId() + "");
        map.put("customer", oceanBulkCargo.getCustomer());
        map.put("customer_code", oceanBulkCargo.getCustomerCode());
        map.put("bill_code", oceanBulkCargo.getBillCode());
        map.put("services", oceanBulkCargo.getServices());
        map.put("fba", oceanBulkCargo.getFba());
        map.put("contacts", oceanBulkCargo.getContacts());
        map.put("phone", oceanBulkCargo.getPhone());
        map.put("addr1", oceanBulkCargo.getAddr1());
        map.put("state", oceanBulkCargo.getState());
        map.put("apply_customs_type", oceanBulkCargo.getApplyCustomsType());
        map.put("customs_clearance", oceanBulkCargo.getCustomsClearance());
        map.put("pay_taxes_type", oceanBulkCargo.getPayTaxesType());
        map.put("delivery_terms", oceanBulkCargo.getDeliveryTerms());
        map.put("purchase_insurance", oceanBulkCargo.getPurchaseInsurance());
        map.put("box_nums_total", oceanBulkCargo.getBoxNumsTotal() + "");
        map.put("box_volume", oceanBulkCargo.getBoxVolume() + "");
        map.put("box_weight", oceanBulkCargo.getBoxWeight() + "");
        map.put("receive_state", oceanBulkCargo.getReceiveState());
        map.put("pay_state", oceanBulkCargo.getPayState());
        map.put("total", oceanBulkCargo.getTotal()+"");
        map.put("total_unit", oceanBulkCargo.getTotalUnit());
        map.put("customer_remark", oceanBulkCargo.getCustomer());
        map.put("remark", oceanBulkCargo.getRemark());
        map.put("annex", oceanBulkCargo.getAnnex());
        map.put("salesmanId", oceanBulkCargo.getSalesmanId()+"");
        map.put("salesman", oceanBulkCargo.getSalesman());
        map.put("operaterId", oceanBulkCargo.getOperaterId()+"");
        map.put("operater", oceanBulkCargo.getOperater());
        map.put("create_user", loginUser.getUserId() + "");
        getMapParameter(sql, map);
        getMapParameter(sql, map);
        int retVal = this.jdbcTemplate.update(sql.toString(), new SqlParameterSource() {
            @Override
            public boolean hasValue(String paramName) {
                return map.containsKey(paramName);
            }

            @Override
            public Object getValue(String paramName) throws IllegalArgumentException {
                return map.get(paramName);
            }
        }, generatedKeyHolder);

        Integer id = generatedKeyHolder.getKey().intValue();
        return id;
    }

    @Override
    public boolean update(OceanBulkCargo oceanBulkCargo, User loginUser) {
        Map<String, String> map = new HashMap<String, String>();
        StringBuffer sql = new StringBuffer("update chf_ocean_bulk_cargo set ");
        sql.append("customer_code=:customer_code,services=:services,fba=:fba,contacts=:contacts ");
        sql.append(",phone=:phone,addr1=:addr1 ");
        sql.append(",state=:state,apply_customs_type=:apply_customs_type ");
        sql.append(",customs_clearance=:customs_clearance,pay_taxes_type=:pay_taxes_type,delivery_terms=:delivery_terms ");
        sql.append(",purchase_insurance=:purchase_insurance,box_nums_total=:box_nums_total,box_volume=:box_volume ");
        sql.append(",total=:total,total_unit=:total_unit");
        sql.append(",receive_state=:receive_state,pay_state=:pay_state,customer_remark=:customer_remark,annex=:annex");
        sql.append(",box_weight=:box_weight,remark=:remark,update_time=now() ");
        sql.append(",update_user=:update_user where id=:id ");
        map.put("customer_code", oceanBulkCargo.getCustomerCode());
        map.put("services", oceanBulkCargo.getServices());
        map.put("fba", oceanBulkCargo.getFba());
        map.put("contacts", oceanBulkCargo.getContacts());
        map.put("phone", oceanBulkCargo.getPhone());
        map.put("addr1", oceanBulkCargo.getAddr1());
        map.put("state", oceanBulkCargo.getState());
        map.put("apply_customs_type", oceanBulkCargo.getApplyCustomsType());
        map.put("customs_clearance", oceanBulkCargo.getCustomsClearance());
        map.put("pay_taxes_type", oceanBulkCargo.getPayTaxesType());
        map.put("delivery_terms", oceanBulkCargo.getDeliveryTerms());
        map.put("purchase_insurance", oceanBulkCargo.getPurchaseInsurance());
        map.put("box_nums_total", oceanBulkCargo.getBoxNumsTotal() + "");
        map.put("box_volume", oceanBulkCargo.getBoxVolume() + "");
        map.put("box_weight", oceanBulkCargo.getBoxWeight() + "");
        map.put("receive_state", oceanBulkCargo.getReceiveState());
        map.put("pay_state", oceanBulkCargo.getPayState());
        map.put("customer_remark", oceanBulkCargo.getCustomer());
        map.put("remark", oceanBulkCargo.getRemark());
        map.put("annex", oceanBulkCargo.getAnnex());
        map.put("total", oceanBulkCargo.getTotal()+"");
        map.put("total_unit", oceanBulkCargo.getTotalUnit());
        map.put("update_user", loginUser.getUserId() + "");
        map.put("id", oceanBulkCargo.getId());
        getMapParameter(sql, map);
        int count = jdbcTemplate.update(sql.toString(), map);
        return count > 0;
    }

    @Override
    public boolean remove(String id, User loginUser) {
        int count = 0;
        Map<String, String> map = null;
        if (id.length() > 0) {
            for (String id1 : id.split(",")) {
                map = new HashMap<String, String>();
                if (id1 == null || id1.equals("")) {
                    break;
                }
                StringBuffer sql = new StringBuffer("update chf_ocean_bulk_cargo set deleted=1" +
                        ",update_user=:update_user, update_time=now() "
                        + " where id =:id ");
                map.put("update_user", loginUser.getUserId() + "");
                map.put("id", id1);
                getMapParameter(sql, map);
                count = jdbcTemplate.update(sql.toString(), map);
            }
        }
        return count > 0;
    }
}
